You are here:

Custom Sets

The Pyramid application provides the ability for end users to define and use custom sets in the application. A custom set is a user defined collection of elements, written in MDX, that can be used as a convenient method for selecting multiple objects anywhere within the query. Using MDX functions, custom sets can become powerful automation tools for dynamically selecting relevant items using logic rather than a static listing of items.

To help users define custom sets, the application comes with a Custom Set MDX editor complete with cube meta data structures, MDX function library and script formatting capabilities.

Professional and Analyst users, with no extended security limitations, can define and use custom sets like server defined named sets in the cube. Consumer users can use custom sets defined by other user types only.

Custom Set definitions can be written with any form of MDX that is compliant with Microsoft's 2005/2008 MDX standards.

The main MDX Editor and Custom Member editors are similar to the Custom Set editor, but expose different features.

NOTE: This manual includes a small reference library for all MDX functions available. However, it is beyond the scope of this manual to provide an in-depth reference. Other third party sources should be used for reference purposes. See the MDX Function Listing for more detail.

Accessing the Custom Set Editor

The Custom Set Editor can be invoked from a variety of places by Professional and Analyst users :

The query ribbon Custom MDX drop down menu.
The shortcut menu in the element tree.
The right-click context menu within the element tree.
- If users select elements within the element tree and choose to create a custom set, the application will auto-fill the query window with the selection
- This also exposes a menu driven wizard for building a variety of pre-defined custom set calculations.

Using & Maintaining Custom Sets

Once created, custom sets will appear at the bottom of the relevant hierarchy's element tree in the Custom Elements folder. From here, custom sets can be used in queries like any other element in the hierarchy.
To Edit a custom set, users can right click on the custom set and choose "edit custom set" from the context menu.
Users can Delete custom sets if the custom element if NOT being used in a report, another custom set or custom member. Warnings will be provided in the case of conflicts.

Custom Set Editor Window

The Custom Set Editor window exposes 4 main sections:

Custom Set property settings (highlighted in purple) which exposes settings for the custom set,
A format bar (highlighted in green) at the top which allows users to choose the font used for the query window
The query window that contains the query text (highlighted in red)
The Meta data and function library (highlighted in orange) which exposes the entire cube structure and the full MDX function library

Initially, the meta data section is collapsed. Click the expander button (blue arrow) to show/hide the section. Once exposed, users can drag and drop items from each of the 3 panels into the query window.

Items dragged from the hierarchy (left) or element (middle) trees will result in the UNIQUENAME of the selected name being added to the query window. This includes dimensions, hierarchies, levels and member elements.
Items dragger from the function list (right) will result in the functions GENERIC FORMULATION being dropped into the query window.

Other features

To choose a different element list in the middle, double click the relevant hierarchy in the left hand panel.
Hovering over elements in the middle panel will highlight the element's tool-tips and its uniquename.
Hovering over the functions will show its formulation as a tool-tip
Double clicking elements in the middle panel or functions in the right panel will insert them into the query window at the current caret position

Once a custom set has been defined, users can click the TEST MDX button to check their syntax and the CREATE button to commit their logic to the server.

Formulating a Custom Set

In defining a custom set, users are effectively creating a statement that will be executed in the MDX engine with the following standard syntax:

SET [Custom Set Name] as formulation

The calculation definition added into the query window is the "formulation" aspect of the above statement. To enable this functionality, the following items should be in place:

All Custom sets must have a name (top left hand corner) and can optionally be given a description.
Each custom set must have a designated parent dimension and hierarchy .Select these from the drop down lists.
Multi-mode is a special Pyramid-only flag for the application of parameterized custom member calculations.

NOTE: it is good practice to encase the set formulation WITHIN braces "{ ........... }"

Custom Set Accessibility

All custom elements (members or set) can be secured.

Private access ensures that the definition is only available to the user that created it. If a private calculation is used in public content, other users will be able to run queries using the item, but they CANNOT change or edit its definition.
Public access allows any user with access to this cube and READ access to the element's definition to consume the definition. If the user also has write access, they can edit and update the definition as well.